import pymysql, os, json
from dbutils.pooled_db import PooledDB


def query_mysql_link():
    """获取到对应的config文件进行链接数据"""
    BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    config_file = os.path.join(BASE_DIR, 'SaaSDeveMySqlConfig', 'database.json')
    with open(config_file) as f:
        database_config = json.load(f)
    return database_config["mysql_link"]


class DB:
    def __init__(self, query_mysql):
        self.pool = self.create_pool(query_mysql)

    def create_pool(self, query_mysql):
        pool = PooledDB(
            creator=pymysql,
            host=query_mysql["HOST"],
            port=query_mysql["PORT"],
            user=query_mysql["USER"],
            password=query_mysql["PASSWORD"],
            db=query_mysql["NAME"],
            autocommit=query_mysql['autocommit'],
            charset=query_mysql["charset"],
            cursorclass=pymysql.cursors.DictCursor,
            blocking=query_mysql["blocking"],  # 设置为阻塞模式，当连接池中没有可用连接时，会阻塞等待
            maxconnections=query_mysql["maxconnections"],  # 连接池中的最大连接数
            mincached=query_mysql["mincached"],  # 初始化时连接池中的连接数
            maxcached=query_mysql["maxcached"],  # 连接池中最多闲置的连接数
            maxusage=query_mysql["maxusage"],  # 每个连接的最大重复使用次数，None表示无限制
            setsession=query_mysql["setsession"],  # 执行连接初始化命令的SQL语句列表
            ping=query_mysql["ping"],  # 检查连接是否有效的时间间隔，0表示不检查
        )
        return pool

    def get_connection(self):
        return self.pool.connection()

    def reConnect(self):
        try:
            self.conn.ping()
        except:
            self.conn = self.get_connection()
            self.cursor = self.conn.cursor()

    # 修改 删除 添加
    def update(self, sql):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(sql)
        lastrowid = cursor.lastrowid
        db.commit()
        cursor.close()
        conn.close()
        return lastrowid

    def update1(self, sql):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(sql)
        cursor.close()
        conn.close()

    # 查询单条数据
    def findOne(self, sql):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(sql)
        res = cursor.fetchone()
        cursor.close()
        conn.close()
        return res

    # 获取全部数据
    def findAll(self, sql):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(sql)
        res = cursor.fetchall()
        cursor.close()
        conn.close()
        return res

    # 批量添加
    def executemany(self, sql, params_list):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.executemany(sql, params_list)
        lastrowid = cursor.lastrowid
        db.commit()
        cursor.close()
        conn.close()
        return lastrowid

    def commit(self):
        pass  # 连接池的连接是自动提交的，无需手动提交

    def rollback(self):
        pass  # 连接池的连接是自动提交的，无需手动回滚


data = query_mysql_link()
db = DB(data)

